#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Time       :   12:39
# Author     : Y4
# Site       : 
# File       : 插入城市.py

import json
import uuid
import datetime

import pymysql


# 读取文件
def load_data():
    with open('cities.json', 'r', encoding="utf-8") as f:
        json_str = f.read()
        json_obj = json.loads(json_str)
    return json_obj


# 插入数据
def insert_cities(data):
    cities = data.get("returnValue")

    # 连接数据库
    db = pymysql.Connect(
        host="localhost",
        port=3306, user="root",
        password="123456",
        database="yjh_users",
        charset="utf8"
    )
    cursor = db.cursor()

    keys = cities.keys()
    for key in keys:
        # 插入当前字母
        _uuid = uuid.uuid4().hex
        _time = datetime.datetime.now()

        cursor.execute(
            f"INSERT INTO mark(uuid, create_at, update_at, letter) VALUES ('{_uuid}','{_time}','{_time}','{key}')")

        db.commit()

        # 获取当前字母的id
        cursor.execute(f"select mark.id from mark where letter='{key}' ")
        mark_id = cursor.fetchone()[0]

        # 获取当前字母的values
        values = cities.get(key)
        for value in values:
            parent_id = value.get('parentId')
            region_name = value.get('regionName')
            city_code = value.get('cityCode')
            pin_yin = value.get('pinYin')

            cursor.execute(
                f"INSERT INTO cities(uuid, create_at, update_at, parent_id, region_name, city_code, pin_yin, mark_id) VALUES ('{_uuid}','{_time}','{_time}',{parent_id},'{region_name}',{city_code},'{pin_yin}',{mark_id});")
            db.commit()


if __name__ == '__main__':
    result = load_data()
    insert_cities(result)
